MySQL的优化细节

数据库设计

目的

结合DBMS(数据库管理系统)实现有效存储、高效访问。减少数据冗余，避免维护异常，节约存储空间。

大概的步骤

需求分析->逻辑设计->物理设计(考虑数据库系统的差异)->维护优化(新需求建表，索引，拆分)。

需求分析

理清楚实体及实体之间的关系(一对一，一对多，多对多)，实体包含的属性，哪些属性(或者属性组合)可以唯一标识一个实体

数据库设计的三大范式

第一范式

如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。第一范式要求数据库中的表都是二维表

就比如说，如果有一个表的列名是籍贯，里面存储的数据是广东广州等等，好了那么这里有一个问题，查询的时候，我要怎么查所有籍贯是广东省的？或许可以用模糊查询，但是这不是最好的解决办法，如果是只查广东广州呢？如果别的省份也有广州呢？

所以说最好的解决办法是把省份和城市分开。

第二范式

数据库的表中不存在非关键字段对任意候选关键字段的部分函数依赖。

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

所有单关键字段的表都符合第二范式

对于某些多对多关系的表，需要分开存储并且使用外键关联。

第三范式

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

第二范式的基础上解决了传递依赖

BC范式

在第三范式的基础之上，数据库表如果不存在任何字段对任意候选关键字段的传递函数依赖则符合BC范式。

确保数据表中的每一列数据都和主键和候选关键字直接相关，而不能间接相关

物理设计

MySQL存储引擎

主流的有MyISAM和InnoDB

MyISAM

不支持事务处理，并发插入的表级锁，一般不用于频繁读写。

Innodb

MVCC行级锁，适用于大部分情况。

表和字段命名规则

- 可读性原则
使用大小写，某些数据库系统对大小写敏感。

- 表意性原则
名称应该表示功能

- 长名原则
尽量使用全名

 字段类型的选择原则

列的数据类型一方面影响了数据存储空间的开销，另一方面也会影响数据查询性能。当一个列可以选择多种数据类型的时候
- 首先考虑数字类型
- 其次考虑日期或者二进制类型
- 最后是字符类型
- 相同级别的数据应该优先选择占用空间小的，比如char varchar 之间应该选择varchar

原因：
- 数据进行比较(查询、JOIN、排序)时，相同数据，字符处理比数字慢
- 在数据库中，数据处理以页为单位，列的长度越小，利于性能提升。

char 和 varchar

1. 列中数据长度差不多的应该考虑char
2. 列中数据长度小于50 byte一般也用char。(列很少用除外，为了节省空间和减少IO，还是应该考虑varchar)
3. 一般不宜定义大于50 B的char

decimal和float

1. decimal(8个字节)存储精确数据
2. float开销小(4个字节)

时间类型

- int存储
字段长度比datetime小，但是使用不方便，要函数转换。只能存储到2038-1-19。
- 时间粒度
考虑时间粒度选择适合的类型值

主键选择

1. 业务主键和数据库主键
业务主键用于标识业务数据，进行表与表之间的关联。
数据库主键为了优化数据存储
Innodb会默认生成6字节的隐含主键，所以尽量自定义主键提高存储效率

2. 根据数据库类型，考虑逐渐是否要顺序增长
有些数据库是按主键顺序逻辑存储的

3. 主键字段类型占用空间要尽可能小。
对于使用聚集索引方式存储的表，每个索引猴都会附加主键信息

 避免使用外键约束

- 降低数据导入效率
- 增加维护成本‘
- 虽然不使用约束，但是相关联的列上一定要建立索引

 避免使用触发器

- 降低数据导入效率
- 可能出现意想不到的数据异常
- 使业务逻辑变得复杂

 关于预留字段

- 无法准确知道预留字段的类型
- 无法准确知道所存储的内容
- 后期维护字段成本和增加字段成本相同

严禁使用预留字段

 反范式化

通过数据冗余增加存取效率，简化查询语句。空间换取时间。
- 减少关联表数量
- 增加读取效率
- 要适度

 维护及优化

- 可以增加表或者字段的备注
- 经常查询的列要加索引，索引不要包括太长的数据类型
- 过多的索引会降低读和写的效率，所以要定期维护索引碎片，SQL语句中不要使用强制索引关键字。
- 变更表结构控制表的宽度和大小，同时对数据字典进行维护
- 尽量不使用select *
- 控制用户使用自定义函数
- 不要使用全局索引

 表拆分

垂直拆分

- 经常查询的列放到一起
- text, blob等大字段拆分到另一附加表中

水平拆分

- 通过主键哈希平均分成几分